package db

import (
	"fmt"
	"reflect"
	"strings"
	"time"

	"gitee.com/miajio/go-util/lib/read"
	"github.com/go-basic/uuid"
)

const (
	PrimaryKeyIsEmpty = "primary key is empty"
)

// AutoSQLEngineInterface 自动生成SQL语句引擎接口
type AutoSQLEngineInterface interface {
	SetPrimaryKeyAutoFunc(f func() string)                                                                                                               // SetPrimaryKeyAutoFunc 设置主键自动生成方法
	Insert(table string, adds ...interface{}) (sql string, params []interface{}, err error)                                                              // Insert 自动生成insert语句
	SelectByPrimaryKey(table string, sele interface{}) (sql string, params []interface{}, err error)                                                     // SelectByPrimaryKey 自动生成依据主键查询结构体数据sql语句
	UpdateByPrimaryKey(table string, update interface{}) (sql string, params []interface{}, err error)                                                   // UpdateByPrimaryKey 自动生成依据主键修改结构体数据sql语句
	DeleteByPrimaryKey(table string, delete interface{}) (sql string, params []interface{}, err error)                                                   // DeleteByPrimaryKey 自动生成依据主键删除结构体数据sql语句
	insertPrepare(adds ...interface{}) [][]Column                                                                                                        // insertPrepare insert语句生成前的准备工作
	getColumns(autoPrimaryKey bool, val interface{}) []Column                                                                                            // getColumns 获取列字段数据
	columnsFilter(autoPrimaryKey bool, isPrimaryKey bool, columnMap map[string]bool, columnNames []string, val []interface{}, columns []Column) []Column // columnsFilter 过滤重复字段
}

// AutoSQLEngine 自动生成SQL语句引擎结构体 -> 实际主体方法由其提供
type AutoSQLEngine struct {
	PrimaryKeyColumnTag string             // id字段列标签
	ColumnTag           string             // 列标签
	Val                 interface{}        // 主表结构体
	PrimaryKeyAutoType  PrimaryKeyAutoType // 主键自动生成方式
	primaryKeyAutoFunc  func() string      // 主键自动生成方法 -> 由用户自定义时设定
}

// SetPrimaryKeyAutoFunc 设置主键自动生成方法
func (autoSQLModel *AutoSQLEngine) SetPrimaryKeyAutoFunc(f func() string) {
	autoSQLModel.primaryKeyAutoFunc = f
}

// Insert 自动生成insert语句
// table	写入的表名
// adds 	需生成sql语句的对象数组
// sql 		返回的sql语句
// params 	返回对应值数组
// err 		如果生成错误或其它异常情况时返回
func (autoSQLModel *AutoSQLEngine) Insert(table string, adds ...interface{}) (sql string, params []interface{}, err error) {
	if len(adds) == 0 {
		return
	}

	sqlBuild := `INSERT INTO %s (%s) VALUES (%s)`
	if len(adds) > 1 {
		sqlBuild = sqlBuild + ";"
	}
	addParams := autoSQLModel.insertPrepare(adds...)
	haveInsert := false
	for i := range addParams {
		addParam := addParams[i]
		placeholder := make([]string, 0)
		columnNames := make([]string, 0)
		columnValues := make([]interface{}, 0)

		for j := range addParam {
			column := addParam[j]
			columnNames = append(columnNames, column.Name)
			placeholder = append(placeholder, "?")
			columnValues = append(columnValues, column.Value)
		}
		if len(columnNames) > 0 {
			resultSql := fmt.Sprintf(sqlBuild, table, strings.Join(columnNames, ","), strings.Join(placeholder, ","))
			sql = sql + resultSql
			params = append(params, columnValues...)
			haveInsert = true
		}
	}
	if haveInsert {
		return
	}
	return sql, params, fmt.Errorf("insert data is empty")
}

// SelectByPrimaryKey 自动生成依据主键查询结构体数据sql语句
// table	查询的表名
// update	被修改的参数结构体
// sql		生成的sql语句
// params	生成对应占位符的值
// err		如果生成错误或其它异常情况时返回
func (autoSQLModel *AutoSQLEngine) SelectByPrimaryKey(table string, sele interface{}) (sql string, params []interface{}, err error) {
	if read.IsZero(sele) {
		return sql, params, fmt.Errorf("select struct is empty")
	}
	if !read.TypeEquals(sele, autoSQLModel.Val) {
		return sql, params, fmt.Errorf("select struct is mismatch")
	}
	sqlBuild := `SELECT %s FROM %s WHERE %s`

	columns := autoSQLModel.getColumns(false, sele)

	// 获取所有字段名称
	columnPrimaryKeyNames, _ := read.ReadAllTag(sele, autoSQLModel.PrimaryKeyColumnTag)
	columnNames, _ := read.ReadAllTag(sele, autoSQLModel.ColumnTag)
	columnNameMap := make(map[string]bool)
	selectColumnNames := make([]string, 0)

	for i := range columnPrimaryKeyNames {
		columnNameMap[columnPrimaryKeyNames[i]] = true
	}

	for i := range columnNames {
		columnNameMap[columnNames[i]] = true
	}

	for column := range columnNameMap {
		selectColumnNames = append(selectColumnNames, column)
	}

	whereColumns := make([]string, 0)
	whereParams := make([]interface{}, 0)

	for i := range columns {
		column := columns[i]
		// 如果是主键 则写入where
		if column.IsPrimaryKey {
			if read.IsZero(column.Value) {
				continue
			}
			whereColumns = append(whereColumns, column.Name+" = ?")
			whereParams = append(whereParams, column.Value)
		}
	}
	if len(whereColumns) == 0 {
		return sql, params, fmt.Errorf(PrimaryKeyIsEmpty)
	}
	sql = fmt.Sprintf(sqlBuild, strings.Join(selectColumnNames, ","), table, strings.Join(whereColumns, " and "))
	params = append(params, whereParams...)
	return
}

// UpdateByPrimaryKey 自动生成依据主键修改结构体数据sql语句
// table	修改的表名
// update	被修改的参数结构体
// sql		生成的sql语句
// params	生成对应占位符的值
// err		如果生成错误或其它异常情况时返回
func (autoSQLModel *AutoSQLEngine) UpdateByPrimaryKey(table string, update interface{}) (sql string, params []interface{}, err error) {
	if read.IsZero(update) {
		return sql, params, fmt.Errorf("update struct is empty")
	}
	if !read.TypeEquals(update, autoSQLModel.Val) {
		return sql, params, fmt.Errorf("update struct is mismatch")
	}
	sqlBuild := `UPDATE %s SET %s WHERE %s`

	columns := autoSQLModel.getColumns(false, update)

	setColumns := make([]string, 0)
	whereColumns := make([]string, 0)
	setParams := make([]interface{}, 0)
	whereParams := make([]interface{}, 0)
	for i := range columns {
		column := columns[i]
		// 如果是主键 则写入where 否则写入 set
		if column.IsPrimaryKey {
			if read.IsZero(column.Value) {
				continue
			}
			whereColumns = append(whereColumns, column.Name+" = ?")
			whereParams = append(whereParams, column.Value)
		} else {
			setColumns = append(setColumns, column.Name+" = ?")
			setParams = append(setParams, column.Value)
		}
	}
	if len(whereColumns) == 0 {
		return sql, params, fmt.Errorf(PrimaryKeyIsEmpty)
	}
	sql = fmt.Sprintf(sqlBuild, table, strings.Join(setColumns, ","), strings.Join(whereColumns, " and "))
	params = append(params, setParams...)
	params = append(params, whereParams...)
	return
}

// DeleteByPrimaryKey 自动生成依据主键删除结构体数据sql语句
// table	删除的表名
// delete	被修改的参数结构体
// sql		生成的sql语句
// params	生成对应占位符的值
// err		如果生成错误或其它异常情况时返回
func (autoSQLModel *AutoSQLEngine) DeleteByPrimaryKey(table string, delete interface{}) (sql string, params []interface{}, err error) {
	if read.IsZero(delete) {
		return sql, params, fmt.Errorf("delete struct is empty")
	}
	if !read.TypeEquals(delete, autoSQLModel.Val) {
		return sql, params, fmt.Errorf("delete struct is mismatch")
	}

	sqlBuild := `DELETE FROM %s WHERE %s`

	columns := autoSQLModel.getColumns(false, delete)
	whereColumns := make([]string, 0)
	whereParams := make([]interface{}, 0)

	for i := range columns {
		column := columns[i]
		// 如果是主键 则写入where
		if column.IsPrimaryKey {
			if read.IsZero(column.Value) {
				continue
			}
			whereColumns = append(whereColumns, column.Name+" = ?")
			whereParams = append(whereParams, column.Value)
		}
	}
	if len(whereColumns) == 0 {
		return sql, params, fmt.Errorf(PrimaryKeyIsEmpty)
	}
	sql = fmt.Sprintf(sqlBuild, table, strings.Join(whereColumns, " and "))
	params = append(params, whereParams...)
	return
}

// insertPrepare insert语句生成前的准备工作
// adds 	需生成sql语句的对象数组
func (autoSQLModel *AutoSQLEngine) insertPrepare(adds ...interface{}) [][]Column {
	result := make([][]Column, 0)

	for i := range adds {
		if read.TypeEquals(autoSQLModel.Val, adds[i]) {
			// 单条insert的key values
			val := adds[i]
			columns := autoSQLModel.getColumns(true, val)
			result = append(result, columns)
		}
	}
	return result
}

// getColumns 获取列字段数据
// autoPrimaryKey	是否自动生成主键数据
// val				带数据结构体
func (autoSQLModel *AutoSQLEngine) getColumns(autoPrimaryKey bool, val interface{}) []Column {
	columns := make([]Column, 0)
	columnMap := make(map[string]bool)

	primaryKeys, primaryKeyValues := read.ReadAllTag(val, autoSQLModel.PrimaryKeyColumnTag)
	columnNames, columnValues := read.ReadHaveValueTag(val, autoSQLModel.ColumnTag)
	// 过滤重复数据并生成字段列表数据
	columns = autoSQLModel.columnsFilter(autoPrimaryKey, true, columnMap, primaryKeys, primaryKeyValues, columns)
	columns = autoSQLModel.columnsFilter(autoPrimaryKey, false, columnMap, columnNames, columnValues, columns)
	return columns
}

// columnsFilter 过滤重复字段
// autoPrimaryKey	是否自动生成主键
// columnMap		记录所有key是否存在的map
// columnNames		字段名
// val				值
// columns 			生成对应的字段结构体对象
func (autoSQLModel *AutoSQLEngine) columnsFilter(autoPrimaryKey bool, isPrimaryKey bool, columnMap map[string]bool, columnNames []string, val []interface{}, columns []Column) []Column {
	for i := range columnNames {
		column := Column{
			IsPrimaryKey: isPrimaryKey,           // 是否是主键
			Name:         columnNames[i],         // 字段名
			Value:        val[i],                 // 值
			TypeOf:       reflect.TypeOf(val[i]), // 字段类型
		}
		// 如果是组件 则判断是否为内部处理逻辑等
		if autoPrimaryKey && column.IsPrimaryKey {
			switch autoSQLModel.PrimaryKeyAutoType {
			case None, AutoNumber:
				// 如果当前字段是0值则不做处理
				if read.IsZero(val[i]) {
					continue
				}
			case AutoUUID:
				// 如果当前字段是0值则生成一个UUID
				if read.IsZero(val[i]) {
					column.Value = strings.ToUpper(strings.ReplaceAll(uuid.New(), "-", ""))
				}
			case AutoTime:
				// 如果当前字段是0值则将当前时间戳写入
				if read.IsZero(val[i]) {
					column.Value = time.Now().Unix()
				}
			case AutoFunc:
				// 如果当前字段是0值则调用自动生成方法去创建一个数据
				if read.IsZero(val[i]) {
					column.Value = autoSQLModel.primaryKeyAutoFunc()
				}
			}
		}
		if !columnMap[column.Name] {
			columns = append(columns, column)
			columnMap[column.Name] = true
		}
	}
	return columns
}
